package com.schoolhelper.task.mapper;

import com.schoolhelper.task.domain.TaskOrder;
import org.apache.ibatis.annotations.*;
import java.util.List;

/**
 * 任务订单数据访问层接口
 * 提供任务订单的数据库操作方法
 */
@Mapper
public interface TaskOrderMapper {
    
    /**
     * 创建新的任务订单
     * @param taskOrder 任务订单对象
     * @return 影响的行数
     */
    @Insert("INSERT INTO task_orders (title, description, category, price, required_time, publisher_id, status, created_time, updated_time) " +
            "VALUES (#{title}, #{description}, #{category}, #{price}, #{requiredTime}, #{publisherId}, #{status}, #{createdTime}, #{updatedTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertTaskOrder(TaskOrder taskOrder);
    
    /**
     * 根据ID查询任务订单
     * @param id 订单ID
     * @return 任务订单对象
     */
    @Select("SELECT id, title, description, category, price, required_time as requiredTime, " +
            "publisher_id as publisherId, accepter_id as accepterId, status, " +
            "accepted_time as acceptedTime, created_time as createdTime, updated_time as updatedTime " +
            "FROM task_orders WHERE id = #{id}")
    TaskOrder selectTaskOrderById(Long id);
    
    /**
     * 查询所有任务订单
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "p.username as publisherName, p.avatar as publisherAvatar, " +
            "a.username as accepterName, a.avatar as accepterAvatar " +
            "FROM task_orders t " +
            "LEFT JOIN users p ON t.publisher_id = p.id " +
            "LEFT JOIN users a ON t.accepter_id = a.id " +
            "ORDER BY t.created_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
        @Result(property = "createdTime", column = "createdTime"),
        @Result(property = "updatedTime", column = "updatedTime"),
        @Result(property = "publisherName", column = "publisherName"),
        @Result(property = "publisherAvatar", column = "publisherAvatar"),
        @Result(property = "accepterName", column = "accepterName"),
        @Result(property = "accepterAvatar", column = "accepterAvatar")
    })
    List<TaskOrder> selectAllTaskOrders();
    
    /**
     * 根据状态查询任务订单
     * @param status 订单状态
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "p.username as publisherName, p.avatar as publisherAvatar, " +
            "a.username as accepterName, a.avatar as accepterAvatar " +
            "FROM task_orders t " +
            "LEFT JOIN users p ON t.publisher_id = p.id " +
            "LEFT JOIN users a ON t.accepter_id = a.id " +
            "WHERE t.status = #{status} ORDER BY t.created_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
        @Result(property = "createdTime", column = "createdTime"),
        @Result(property = "updatedTime", column = "updatedTime"),
        @Result(property = "publisherName", column = "publisherName"),
        @Result(property = "publisherAvatar", column = "publisherAvatar"),
        @Result(property = "accepterName", column = "accepterName"),
        @Result(property = "accepterAvatar", column = "accepterAvatar")
    })
    List<TaskOrder> selectTaskOrdersByStatus(String status);
    
    /**
     * 根据发布者ID查询任务订单
     * @param publisherId 发布者用户ID
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "u.username as accepterName, u.avatar as accepterAvatar " +
            "FROM task_orders t LEFT JOIN users u ON t.accepter_id = u.id " +
            "WHERE t.publisher_id = #{publisherId} ORDER BY t.created_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
        @Result(property = "createdTime", column = "createdTime"),
        @Result(property = "updatedTime", column = "updatedTime"),
        @Result(property = "accepterName", column = "accepterName"),
        @Result(property = "accepterAvatar", column = "accepterAvatar")
    })
    List<TaskOrder> selectTaskOrdersByPublisherId(Long publisherId);
    
    /**
     * 根据接单者ID查询任务订单
     * @param accepterId 接单者用户ID
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "u.username as publisherName, u.avatar as publisherAvatar " +
            "FROM task_orders t LEFT JOIN users u ON t.publisher_id = u.id " +
            "WHERE t.accepter_id = #{accepterId} ORDER BY t.created_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
         @Result(property = "createdTime", column = "createdTime"),
         @Result(property = "updatedTime", column = "updatedTime"),
         @Result(property = "publisherName", column = "publisherName"),
         @Result(property = "publisherAvatar", column = "publisherAvatar")
    })
    List<TaskOrder> selectTaskOrdersByAccepterId(Long accepterId);
    
    /**
     * 根据分类查询任务订单
     * @param category 任务分类
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "p.username as publisherName, p.avatar as publisherAvatar, " +
            "a.username as accepterName, a.avatar as accepterAvatar " +
            "FROM task_orders t " +
            "LEFT JOIN users p ON t.publisher_id = p.id " +
            "LEFT JOIN users a ON t.accepter_id = a.id " +
            "WHERE t.category = #{category} AND t.status = 'OPEN' ORDER BY t.created_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
        @Result(property = "createdTime", column = "createdTime"),
        @Result(property = "updatedTime", column = "updatedTime"),
        @Result(property = "publisherName", column = "publisherName"),
        @Result(property = "publisherAvatar", column = "publisherAvatar"),
        @Result(property = "accepterName", column = "accepterName"),
        @Result(property = "accepterAvatar", column = "accepterAvatar")
    })
    List<TaskOrder> selectTaskOrdersByCategory(String category);
    
    /**
     * 更新任务订单状态
     * @param id 订单ID
     * @param status 新状态
     * @param accepterId 接单者ID（可为空）
     * @return 影响的行数
     */
    @Update("UPDATE task_orders SET status = #{status}, accepter_id = #{accepterId}, accepted_time = #{acceptedTime}, updated_time = NOW() WHERE id = #{id}")
    int updateTaskOrderStatus(@Param("id") Long id, @Param("status") String status, @Param("accepterId") Long accepterId, @Param("acceptedTime") String acceptedTime);
    
    /**
     * 更新任务订单信息
     * @param taskOrder 任务订单对象
     * @return 影响的行数
     */
    @Update("UPDATE task_orders SET title = #{title}, description = #{description}, category = #{category}, " +
            "price = #{price}, required_time = #{requiredTime}, updated_time = NOW() WHERE id = #{id}")
    int updateTaskOrder(TaskOrder taskOrder);
    
    /**
     * 删除任务订单
     * @param id 订单ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM task_orders WHERE id = #{id}")
    int deleteTaskOrder(Long id);
    
    /**
     * 根据关键词搜索任务订单
     * @param keyword 搜索关键词
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "p.username as publisherName, p.avatar as publisherAvatar, " +
            "a.username as accepterName, a.avatar as accepterAvatar " +
            "FROM task_orders t " +
            "LEFT JOIN users p ON t.publisher_id = p.id " +
            "LEFT JOIN users a ON t.accepter_id = a.id " +
            "WHERE (t.title LIKE CONCAT('%', #{keyword}, '%') OR t.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "AND t.status = 'OPEN' ORDER BY t.created_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
        @Result(property = "createdTime", column = "createdTime"),
        @Result(property = "updatedTime", column = "updatedTime"),
        @Result(property = "publisherName", column = "publisherName"),
        @Result(property = "publisherAvatar", column = "publisherAvatar"),
        @Result(property = "accepterName", column = "accepterName"),
        @Result(property = "accepterAvatar", column = "accepterAvatar")
    })
    List<TaskOrder> searchTaskOrders(String keyword);
    
    /**
     * 分页查询任务订单
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 任务订单列表
     */
    @Select("SELECT t.id, t.title, t.description, t.category, t.price, t.required_time as requiredTime, " +
            "t.publisher_id as publisherId, t.accepter_id as accepterId, t.status, " +
            "t.accepted_time as acceptedTime, t.created_time as createdTime, t.updated_time as updatedTime, " +
            "p.username as publisherName, p.avatar as publisherAvatar, " +
            "a.username as accepterName, a.avatar as accepterAvatar " +
            "FROM task_orders t " +
            "LEFT JOIN users p ON t.publisher_id = p.id " +
            "LEFT JOIN users a ON t.accepter_id = a.id " +
            "WHERE t.status = 'OPEN' ORDER BY t.created_time DESC LIMIT #{offset}, #{limit}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "description", column = "description"),
        @Result(property = "category", column = "category"),
        @Result(property = "price", column = "price"),
        @Result(property = "requiredTime", column = "requiredTime"),
        @Result(property = "publisherId", column = "publisherId"),
        @Result(property = "accepterId", column = "accepterId"),
        @Result(property = "status", column = "status"),
        @Result(property = "acceptedTime", column = "acceptedTime"),
        @Result(property = "createdTime", column = "createdTime"),
        @Result(property = "updatedTime", column = "updatedTime"),
        @Result(property = "publisherName", column = "publisherName"),
        @Result(property = "publisherAvatar", column = "publisherAvatar"),
        @Result(property = "accepterName", column = "accepterName"),
        @Result(property = "accepterAvatar", column = "accepterAvatar")
    })
    List<TaskOrder> selectTaskOrdersWithPagination(@Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 统计任务订单总数
     * @return 总数
     */
    @Select("SELECT COUNT(*) FROM task_orders WHERE status = 'OPEN'")
    int countTaskOrders();
    
    /**
     * 批量更新过期任务状态为已取消
     * @return 影响的行数
     */
    @Update("UPDATE task_orders SET status = 'CANCELLED', updated_time = NOW() " +
            "WHERE status = 'OPEN' AND required_time < NOW()")
    int updateExpiredTasksStatus();
    
    /**
     * 批量更新已接单但超时的任务状态为已超时
     * @return 影响的行数
     */
    @Update("UPDATE task_orders SET status = 'TIMEOUT', updated_time = NOW() " +
            "WHERE status = 'ACCEPTED' AND required_time < NOW()")
    int updateAcceptedExpiredTasksStatus();
    
    /**
     * 获取待接单的过期任务列表
     * @return 过期任务列表
     */
    @Select("SELECT id, title, description, category, price, required_time as requiredTime, " +
            "publisher_id as publisherId, accepter_id as accepterId, status, " +
            "accepted_time as acceptedTime, created_time as createdTime, updated_time as updatedTime " +
            "FROM task_orders WHERE status = 'OPEN' AND required_time < NOW()")
    List<TaskOrder> selectExpiredOpenTasks();
    
    /**
     * 获取已接单但超时的任务列表
     * @return 超时任务列表
     */
    @Select("SELECT id, title, description, category, price, required_time as requiredTime, " +
            "publisher_id as publisherId, accepter_id as accepterId, status, " +
            "accepted_time as acceptedTime, created_time as createdTime, updated_time as updatedTime " +
            "FROM task_orders WHERE status = 'ACCEPTED' AND required_time < NOW()")
    List<TaskOrder> selectExpiredAcceptedTasks();
}